{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>d</th>\n",
       "      <th>uid</th>\n",
       "      <th>action</th>\n",
       "      <th>active_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>jobs</td>\n",
       "      <td>324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>feeds</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>3</td>\n",
       "      <td>im</td>\n",
       "      <td>345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>network</td>\n",
       "      <td>765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>3</td>\n",
       "      <td>feeds</td>\n",
       "      <td>342</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           d  uid   action  active_duration\n",
       "0 2021-01-01    1     jobs              324\n",
       "1 2021-01-01    2    feeds              445\n",
       "2 2021-01-01    3       im              345\n",
       "3 2021-01-02    2  network              765\n",
       "4 2021-01-02    3    feeds              342"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取数据集\n",
    "df1 = pd.read_excel('./示例数据.xlsx',sheet_name='data1')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>uid</th>\n",
       "      <th>career_level</th>\n",
       "      <th>city</th>\n",
       "      <th>work_length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>中级</td>\n",
       "      <td>北京</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>高级</td>\n",
       "      <td>上海</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>初级</td>\n",
       "      <td>北京</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>普通</td>\n",
       "      <td>平顶山</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   uid career_level city  work_length\n",
       "0    1           中级   北京            4\n",
       "1    2           高级   上海            7\n",
       "2    3           初级   北京            1\n",
       "3    4           普通  平顶山            2"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取数据集\n",
    "df2 = pd.read_excel('./示例数据.xlsx',sheet_name='data2')\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>d</th>\n",
       "      <th>uid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           d  uid\n",
       "0 2021-01-01    3\n",
       "1 2021-01-02    2"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 初级\n",
    "# 一月中，每日活跃的用户数\n",
    "df = df1[['d','uid']].drop_duplicates()\n",
    "con1 = df['d'] >= '2021/1/1'\n",
    "con2 = df['d'] <= '2021/1/31'\n",
    "df = df[con1 & con2]\n",
    "df = df.groupby('d')['uid'].count().reset_index()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>d</th>\n",
       "      <th>action</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>feeds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>network</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           d   action\n",
       "1 2021-01-01    feeds\n",
       "3 2021-01-02  network"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 初级\n",
    "# 一月中，对于每日活跃用户，其当日活跃时长最大的模块\n",
    "con1 = df1['d'] >= '2021/1/1'\n",
    "con2 = df1['d'] <= '2021/1/31'\n",
    "df = df1[con1 & con2]\n",
    "df['组内降序排名'] = df.groupby(['d'])['active_duration'].rank(method='dense',ascending=False).astype(int)\n",
    "df = df[df['组内降序排名'] == 1]\n",
    "df = df[['d','action']]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>uid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  uid\n",
       "0      0    1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 初级\n",
    "# 元旦期间的活跃用户中，jobs 模块活跃超过 100s 的用户\n",
    "con1 = df1['d'] >= '2021/1/1'\n",
    "con2 = df1['d'] <= '2021/1/3'\n",
    "con3 = df1['action'] == 'jobs'\n",
    "con4 = df1['active_duration'] > 100\n",
    "df = df1[con1 & con2 & con3 & con4]\n",
    "df = df['uid'].drop_duplicates().reset_index()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'50%'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 中级\n",
    "# 昨日的活跃用户中，各个级别人才，每日最大活跃时长模块的活跃时长，占当日总活跃时长的比值的均值\n",
    "today = '2021/1/3'\n",
    "yesterday = (pd.to_datetime(today) - datetime.timedelta(days=1)).strftime('%Y/%m/%d')\n",
    "df = df1[df1['d'] == yesterday]\n",
    "df = pd.merge(df,df2,on='uid',how='left')\n",
    "df = df.groupby(['career_level','action'])['active_duration'].sum().reset_index()\n",
    "df = df.sort_values(by=['active_duration'])\n",
    "df['占比'] = df['active_duration'] / df['active_duration'].sum()\n",
    "df['组内降序排名'] = df.groupby(['career_level'])['active_duration'].rank(method='dense',ascending=False).astype(int)\n",
    "mean_value = str(int(round(df[df['组内降序排名'] == 1]['占比'].mean() * 100,2))) + '%'\n",
    "mean_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "505.5"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 中级\n",
    "# 过去一周内，北京用户的模块活跃总时长的均值\n",
    "today = '2021/1/7'\n",
    "begin_date = (pd.to_datetime(today) - datetime.timedelta(days=7)).strftime('%Y/%m/%d')\n",
    "end_date = (pd.to_datetime(today) - datetime.timedelta(days=1)).strftime('%Y/%m/%d')\n",
    "df = pd.merge(df1,df2,on='uid',how='left')\n",
    "df = df[['d','city','action','active_duration']]\n",
    "con1 = df['d'] >= begin_date\n",
    "con2 = df['d'] <= end_date\n",
    "con3 = df['city'] == '北京'\n",
    "df = df[con1 & con2 & con3]\n",
    "df = df.groupby(['d'])['active_duration'].sum().reset_index()\n",
    "mean_value = df['active_duration'].mean()\n",
    "mean_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>uid</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   uid  rank\n",
       "1    2     2\n",
       "2    3     2"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 高级\n",
    "# 过去一个月内，曾连续 2 天活跃的用户\n",
    "today = '2021/1/30'\n",
    "begin_date = (pd.to_datetime(today) - datetime.timedelta(days=30)).strftime('%Y/%m/%d')\n",
    "end_date = (pd.to_datetime(today) - datetime.timedelta(days=1)).strftime('%Y/%m/%d')\n",
    "con1 = df1['d'] >= begin_date\n",
    "con2 = df1['d'] <= end_date\n",
    "df = df1[con1 & con2]\n",
    "df = df[['d','uid']].drop_duplicates()\n",
    "df['rank'] = df['d'].groupby(df['uid']).rank(method='dense',ascending=True).astype(int)\n",
    "df = df.sort_values(by=['uid','d'])\n",
    "df['days'] = df['rank'].map(lambda x:pd.Timedelta(days=x))\n",
    "df['minus'] = df['d'] - df['days']\n",
    "df_group1 = df.groupby(['uid','minus'])['rank'].max().reset_index()\n",
    "df_group2 = df_group1.groupby('uid')['rank'].max().reset_index()\n",
    "df = df_group2[df_group2['rank'] == 2]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>career_level</th>\n",
       "      <th>uid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>中级</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>初级</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>高级</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  career_level  uid\n",
       "0           中级    1\n",
       "1           初级    2\n",
       "2           高级    2"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 高级\n",
    "# 过去一个月内，不同级别用户的活跃频次差异\n",
    "today = '2021/1/30'\n",
    "begin_date = (pd.to_datetime(today) - datetime.timedelta(days=30)).strftime('%Y/%m/%d')\n",
    "end_date = (pd.to_datetime(today) - datetime.timedelta(days=1)).strftime('%Y/%m/%d')\n",
    "con1 = df1['d'] >= begin_date\n",
    "con2 = df1['d'] <= end_date\n",
    "df = df1[con1 & con2]\n",
    "df = pd.merge(df1,df2,on='uid',how='left')\n",
    "df = df[['d','career_level','uid','action','active_duration']]\n",
    "df = df.groupby(['career_level'])['uid'].count().reset_index()\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
